package com.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.LinkedHashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel工具
 * @author  jinmiao
 *
 */
public class ExcelUtils {
	/**
	 * 读取exce 
	 * 
	 * @param filePath
	 * @return
	 */
	public static Workbook loadExcel(String filePath) {
		Workbook wb = null;
		File file = new File(filePath);
		FileInputStream in = null;
		try {
			in = new FileInputStream(file);
			if (filePath.endsWith(".xls")) {
				wb = (Workbook) new HSSFWorkbook(new POIFSFileSystem(in));
			} else if (filePath.endsWith(".xlsx")) {
				wb = (Workbook) new XSSFWorkbook(in);
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally
		{
			try {
				if(in!=null)
					in.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			if(wb!=null)
				try {
					wb.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
		}
		return wb;
	}
	
	
	
	
	/**
	 * 获得一页的excel校验
	 * 防止策划填错
	 * @param sheet
	 * @return
	 */
	public static Map<Integer, String> getSheetCheck(Sheet sheet,String cellType)
	{
		Map<Integer, String> map = new LinkedHashMap<Integer, String>();
		Row row=sheet.getRow(3);
		if(row==null)
			return map;
		int cellLenth = row.getLastCellNum();
		for(int i =0;i<cellLenth;i++){
			String values = getValuesString(row.getCell(i),cellType);
			if(values==null||values.equals(""))
				continue;
			map.put(i, values);
		}
		return map;
	}
	
	
	
	/**
	 * 获取一页的标题颜色
	 * @param sheet
	 * @return
	 */
	public static Map<Integer, Integer> getSheetColor(Sheet sheet)
	{
		Map<Integer, Integer> map = new LinkedHashMap<Integer, Integer>();
		Row row1=sheet.getRow(0);
		int cellLenth = row1.getLastCellNum();
		for(int i =0;i<cellLenth;i++){
			Cell cell = row1.getCell(i);
			map.put(i, (int)cell.getCellStyle().getFillForegroundColor());
		}
		return map;
	}
	
	
	/**
	 * 获取一行中的值
	 * @param sheet
	 * @param rowNum
	 * @param rowLenth
	 * @return
	 */
	public static Map<Integer, String> getRowValues(Sheet sheet,int rowNum,int cellLenth,Map<Integer, String> cellTypeMap)
	{
		Map<Integer, String> map = new LinkedHashMap<Integer, String>();
		Row row = sheet.getRow(rowNum);
		for(int i=0;i<cellLenth;i++)
		{
			String cellType = null;
			if(cellTypeMap!=null)
				cellType = cellTypeMap.get(i);
			Cell cell = null;
			if(row!=null)
				cell = row.getCell(i);
			map.put(i, getValuesString(cell,cellType).trim());
		}
		return map;
	}
	
	/**
	 * 获得一行的值对应的位置
	 * @param sheet
	 * @param rowNum
	 * @param cellLenth
	 * @return
	 */
	public static Map<String, Integer> getIndexRowValues(Sheet sheet,int rowNum,int cellLenth,String cellType)
	{
		Map<String, Integer> map = new LinkedHashMap<String, Integer>();
		Row row = sheet.getRow(rowNum);
		for(int i=0;i<cellLenth;i++)
		{
			map.put(getValuesString(row.getCell(i),cellType),i );
		}
		return map;
	}
	
	
	public static String getValuesString(Cell cell,String cellType)
	{
		if(cell==null)
			return "";
		if(cellType!=null)
			cellType = cellType.trim();
		if(cellType==null||cellType.equals("string")||cellType.equals("String")||cellType.equals(""))
		{
			cell.setCellType(Cell.CELL_TYPE_STRING);
			return cell.getStringCellValue();
		}
		if(cellType.equals("int"))
		{
			cell.setCellType(Cell.CELL_TYPE_STRING);
			String temp = cell.getStringCellValue();
			if(temp.isEmpty())
				return "";
			double values = Double.parseDouble(temp);
			if(values>0)
			{
				return String.valueOf((int)Math.ceil(values));
			}else
			{
				return String.valueOf((int)Math.floor(values));
			}
		}
		return "";
	}
}
